﻿using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Reflection;
using Devonline.Core;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

namespace Devonline.Database.MySQL;

public static class ServiceExtensions
{
    /// <summary>
    /// 注册数据库上下文
    /// </summary>
    /// <param name="services">依赖注入服务容器</param>
    /// <param name="appSetting">基础配置项</param>
    /// <returns></returns>
    public static IServiceCollection AddMySQL<TDbContext>(this IServiceCollection services, AppSetting appSetting) where TDbContext : DbContext
    {
        if (!string.IsNullOrWhiteSpace(appSetting.ApplicationDbContext))
        {
            var assembly = appSetting.MigrationsAssembly ?? Assembly.GetCallingAssembly().FullName;
            services.AddDbContext<TDbContext>(builder => builder.UseMySql(appSetting.ApplicationDbContext, ServerVersion.AutoDetect(appSetting.ApplicationDbContext), options => options.UseMicrosoftJson().MigrationsAssembly(assembly)), appSetting.ServiceLifetime);
        }

        return services;
    }
    /// <summary>
    /// 注册数据库上下文
    /// </summary>
    /// <param name="services">依赖注入服务容器</param>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="migrationsAssembly">数据迁移作用的程序集</param>
    /// <param name="contextLifetime">数据库上下文的生命周期</param>
    /// <returns></returns>
    public static IServiceCollection AddMySQL<TDbContext>(this IServiceCollection services, string? connectionString = default, string? migrationsAssembly = default, ServiceLifetime contextLifetime = ServiceLifetime.Scoped) where TDbContext : DbContext
    {
        if (!string.IsNullOrWhiteSpace(connectionString))
        {
            var assembly = migrationsAssembly ?? Assembly.GetCallingAssembly().FullName;
            services.AddDbContext<TDbContext>(builder => builder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString), options => options.UseMicrosoftJson().MigrationsAssembly(assembly)), contextLifetime);
        }

        return services;
    }

    /// <summary>
    /// 注册数据库上下文创建对象
    /// </summary>
    /// <param name="builder">依赖注入服务容器</param>
    /// <param name="connectionString">数据库连接字符串</param>
    /// <param name="migrationsAssembly">数据迁移作用的程序集</param>
    /// <returns></returns>
    public static DbContextOptionsBuilder Build(this DbContextOptionsBuilder builder, string? connectionString = default, string? migrationsAssembly = default)
    {
        if (!string.IsNullOrWhiteSpace(connectionString))
        {
            var assembly = migrationsAssembly ?? Assembly.GetCallingAssembly().FullName;
            return builder.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString), options => options.UseMicrosoftJson().MigrationsAssembly(assembly));
        }

        return builder;
    }

    #region 批量数据库操作
    /// <summary>
    /// 使用 insert into table (columns) values (values), (values)... 的语法批量写入数据
    /// 在单次写入数量或字符限制大小某一个达到限制值时即提交一次
    /// </summary>
    /// <typeparam name="T">待写入的数据类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待写入数据</param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public static async Task<int> InsertsAsync<TDbContext, T>(this TDbContext context, IEnumerable<T> data) where TDbContext : DbContext
    {
        if (data is null || !data.Any())
        {
            return 0;
        }

        var type = typeof(T);
        var propertyInfos = type.GetProperties().Where(x => x.HasAttribute<ColumnAttribute>() && !x.HasAttribute<NotMappedAttribute>());
        var total = data.Count();
        var values = new List<string>();

        //并发构造值的集合
        //foreach (var t in data)
        Parallel.ForEach(data, t =>
        {
            try
            {
                var sqls = new List<string>();
                foreach (var propertyInfo in propertyInfos)
                {
                    sqls.Add(t.GetSqlStringValue<T>(propertyInfo) ?? "NULL");
                }

                lock (values)
                {
                    values.Add($"({string.Join(AppSettings.DEFAULT_SPLITER_STRING, sqls)})");
                }

                sqls.Clear();
            }
            catch (Exception ex)
            {
                throw new Exception($"The bulk insert data of {type.Name} error, total: {total}, the entity is: {t?.ToJsonString()}", ex);
            }
        });

        //写入数据库
        if (values.Count <= 0)
        {
            return 0;
        }

        var result = 0;
        var sql = string.Empty;
        var limit = AppSettings.UNIT_THOUSAND;
        var tableName = type.GetTableName();

        try
        {
            //写入前修改数据库和当前表一些设定, 以提高性能
            await context.Database.ExecuteSqlRawAsync($"SET GLOBAL UNIQUE_CHECKS=0; SET GLOBAL FOREIGN_KEY_CHECKS=0; SET AUTOCOMMIT=0; SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT=2; LOCK TABLES `{tableName}` WRITE; ALTER TABLE `{tableName}` DISABLE KEYS;");

            //Whether to replace "insert into" with "insert delayed into"
            var sqlInsert = $"INSERT INTO {tableName} ({string.Join(AppSettings.DEFAULT_SPLITER_STRING, propertyInfos.Select(p => '`' + p.GetColumnName() + '`'))}) VALUES ";
            for (int index = 0; index < values.Count; index += limit)
            {
                var end = index + limit;
                if (end > values.Count)
                {
                    end = values.Count;
                }

                sql = sqlInsert + Environment.NewLine + string.Join(AppSettings.DEFAULT_SPLITER_STRING + Environment.NewLine, values[index..end]) + AppSettings.CHAR_SEMICOLON;
                result += await context.Database.ExecuteSqlRawAsync(sql);
            }

            return result;
        }
        catch (Exception ex)
        {
            throw new Exception($"The last times to bulk insert data of {type.Name} error, total: {total}, current: {result}, the sql is: " + sql, ex);
        }
        finally
        {
            //还原数据库和表设定
            await context.Database.ExecuteSqlRawAsync($"ALTER TABLE `{tableName}` ENABLE KEYS; UNLOCK TABLES; SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT=1; SET AUTOCOMMIT=1; SET GLOBAL FOREIGN_KEY_CHECKS=1; SET GLOBAL UNIQUE_CHECKS=1;");
            values.Clear();
        }
    }
    /// <summary>
    /// 使用 update table set column1=value1,column2=value ... 的语法批量更新数据
    /// 在单次更新数量或字符限制大小某一个达到限制值时即提交一次
    /// </summary>
    /// <typeparam name="T">待更新的数据类型</typeparam>
    /// <typeparam name="TKey">主键类型</typeparam>
    /// <param name="context">数据库上下文</param>
    /// <param name="data">待更新数据</param>
    /// <param name="limit">单次更新限制数量, 默认 1000, 这个值由数据库链接决定</param>
    /// <param name="separators">字段引用符号, 针对字段名是 数据库关键字的情况下, 每种数据库有不同的引用表示方式, 如 SQLServer: [], MySQL:` , 默认无</param>
    /// <returns></returns>
    /// <exception cref="Exception"></exception>
    public static async Task<int> UpdatesAsync<TDbContext, T>(this TDbContext context, IEnumerable<T> data) where TDbContext : DbContext
    {
        if (data is null || !data.Any())
        {
            return 0;
        }

        var type = typeof(T);
        var propertyInfos = type.GetProperties().Where(x => x.HasAttribute<ColumnAttribute>() && !x.HasAttribute<NotMappedAttribute>());

        var total = data.Count();
        var values = new List<string>();
        var sqlUpdate = $"UPDATE {type.GetTableName()} SET ";

        //并发构造值的集合
        Parallel.ForEach(data, t =>
        {
            try
            {
                var sqls = new List<string>();
                var keys = new List<string>();
                foreach (var propertyInfo in propertyInfos)
                {
                    var stringValue = t.GetSqlStringValue<T>(propertyInfo) ?? "NULL";
                    var fieldExpression = '`' + propertyInfo.Name + '`' + AppSettings.CHAR_EQUAL + stringValue;
                    if (propertyInfo.HasAttribute<KeyAttribute>())
                    {
                        keys.Add(fieldExpression);
                    }
                    else
                    {
                        sqls.Add(fieldExpression);
                    }
                }

                lock (values)
                {
                    values.Add(sqlUpdate + string.Join(AppSettings.DEFAULT_SPLITER_STRING, sqls) + " WHERE " + string.Join($" and ", keys));
                }

                sqls.Clear();
                keys.Clear();
            }
            catch (Exception ex)
            {
                throw new Exception($"The last times to bulk update data of {type.Name} error, total: {total}, the entity is: {t?.ToJsonString()}", ex);
            }
        });

        //写入数据库
        if (values.Count <= 0)
        {
            return 0;
        }

        var result = 0;
        var sql = string.Empty;
        int limit = AppSettings.UNIT_THOUSAND;
        var tableName = type.GetTableName();

        try
        {
            //写入前修改数据库和当前表一些设定, 以提高性能
            await context.Database.ExecuteSqlRawAsync($"SET GLOBAL UNIQUE_CHECKS=0; SET GLOBAL FOREIGN_KEY_CHECKS=0; SET AUTOCOMMIT=0; SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT=2; LOCK TABLES `{tableName}` WRITE; ALTER TABLE `{tableName}` DISABLE KEYS;");

            for (int index = 0; index < values.Count; index += limit)
            {
                var end = index + limit;
                if (end > values.Count)
                {
                    end = values.Count;
                }

                sql = string.Join(AppSettings.CHAR_SEMICOLON + Environment.NewLine, values[index..end]) + AppSettings.CHAR_SEMICOLON;
                total += await context.Database.ExecuteSqlRawAsync(sql);
            }

            return total;
        }
        catch (Exception ex)
        {
            throw new Exception($"The last times to bulk update data of {type.Name} error, total: {total}, current: {result}, the sql is: " + sql, ex);
        }
        finally
        {
            //还原数据库和表设定
            await context.Database.ExecuteSqlRawAsync($"ALTER TABLE `{tableName}` ENABLE KEYS; UNLOCK TABLES; SET GLOBAL INNODB_FLUSH_LOG_AT_TRX_COMMIT=1; SET AUTOCOMMIT=1; SET GLOBAL FOREIGN_KEY_CHECKS=1; SET GLOBAL UNIQUE_CHECKS=1;");
            values.Clear();
        }
    }
    #endregion
}